#
# 文件名: bom_compare_v3.py
# 执行: python3 bom_compare_v3.py
#
# 比较两个BOM 文件， 并将分析结果在新生成文件中标记出来。
# 比较分析后的文件为 BOM_COMPARE.xlsx
# 详细报告文件 BOM_REPORT.TXT
# 在 BOM_COMPARE.xlsx中：
# 黄色标记部分 为新文件和老文件有差异的地方。
# 绿色标记部分 为新文件新增内容。（老文件中没有）
# 红色标记部分 为新文件删除内容。（老文件中有）
# 橙色边框标记部分 为 MFR PN 内容包含了空格。
#
# 注意：
# 运行环境为 python3 + openpyxl
#
# 比较后生成文件：
# BOM_REPORT.TXT 详细报告
# BOM_COMPARE.xlsx(全信息，不同颜色标记差异)
# BOM_COMPARE_diff.xlsx(仅保留差异部分信息)
#

import os
from openpyxl import load_workbook
from openpyxl.styles import colors, Font, Fill, NamedStyle
from openpyxl.styles import PatternFill, Border, Side, Alignment

version = '3.8.1'

print(' ____   ___  __  __    ____ ___  __  __ ____   _    ____  _____')
print('| __ ) / _ \|  \/  |  / ___/ _ \|  \/  |  _ \ / \  |  _ \| ____|')
print('|  _ \| | | | |\/| | | |  | | | | |\/| | |_) / _ \ | |_) |  _|')
print('| |_) | |_| | |  | | | |__| |_| | |  | |  __/ ___ \|  _ <| |___')
print('|____/ \___/|_|  |_|  \____\___/|_|  |_|_| /_/   \_\_| \_\_____|')

print(' _____ ___   ___  _')
print('|_   _/ _ \ / _ \| |')
print('  | || | | | | | | |')
print('  | || |_| | |_| | |___')
print('  |_| \___/ \___/|_____|')
print('')
print('Version: ' + version)
print('---------------------------------------------------------------')

diff_count = 0
new_count = 0
remove_count = 0

def deleterows(sheet, row_num):
    for row in range(row_num, sheet.max_row):
        for column in range(sheet.max_column):
            sheet[row][column].value = sheet[row + 1][column].value
    for cell in list(sheet.rows)[sheet.max_row - 1]:
        cell.value = None


old_file = input("Please input OLD bom file name: ")
while not os.path.exists(old_file):
    print('File NOT exist!\n')
    old_file = input("Please input OLD bom file name: ")

new_file = input("Please input NEW bom file name: ")
while not os.path.exists(new_file):
    print('File NOT exist!\n')
    new_file = input("Please input NEW bom file name: ")

# wb1 = load_workbook("BOM_OLD.xlsx")
wb1 = load_workbook(old_file)
# print(wb.sheetnames)
# sheet1 = wb1["Original Bom"]
sheet1 = wb1.active

# wb2 = load_workbook("BOM_NEW.xlsx")
wb2 = load_workbook(new_file)
# sheet2 = wb2["BOM"]
sheet2 = wb2.active

report = open('./BOM_REPORT.TXT', 'w')
report.write('\n' + '-'*60)
report.write('\n' + 'BOM COMPARE REPORT')
report.write('\n' + '-'*60)
report.write('\n')
# 字体
# font1 = Font(name='微软雅黑', size=11, b=True)
# font2 = Font(name='微软雅黑', size=11)

# 边框
line_t = Side(style='thin', color='000000')  # 细边框
line_m = Side(style='medium', color='000000')  # 粗边框
border1 = Border(top=line_m, bottom=line_t, left=line_t, right=line_t)
# 与标题相邻的边设置与标题一样
border2 = Border(top=line_t, bottom=line_t, left=line_t, right=line_t)

#橙色
line_3 = Side(style='thick', color='FACC2E')
border3 = Border(top=line_3, bottom=line_3, left=line_3, right=line_3)

# 填充
# fill = PatternFill('solid', fgColor='ff0000')

# 对齐
alignment = Alignment(horizontal='left', vertical='center')

# sty1 = NamedStyle(name='sty1', font=font1, fill=fill,
#                   border=border1, alignment=alignment)

#yellow
modify_sty = NamedStyle(name='modify_sty', fill=PatternFill('solid', fgColor='ffff00'), alignment=alignment)
#red
remove_sty = NamedStyle(name='remove_sty', fill=PatternFill('solid', fgColor='FF0033'), alignment=alignment)
#green
new_sty = NamedStyle(name='new_sty', fill=PatternFill('solid', fgColor='339933'), alignment=alignment)

#has blank space
has_blank_space_sty = NamedStyle(name='has_blank_space_sty',  border=border3, alignment=alignment)

rows1 = sheet1.max_row
cols1 = sheet1.max_column
rows2 = sheet2.max_row
cols2 = sheet2.max_column

removed_item = []
removed_list = []

set_removed = {}
set_added = {}
# CHANGE ITEM, MARKED YELLOW#########################################
for r1 in range(2, rows1 + 1):
    exist_flag = False
    for c1 in range(1, cols1 + 1):
        info_dict_old = {}
        info_dict_new = {}
        if c1 == 4:
#             print(sheet1.cell(r1,c1).value)
            for r2 in range(2, rows2 + 1):
                for c2 in range(1, cols2 + 1):
                    if c2 == 4 and sheet1.cell(r1, c1).value == sheet2.cell(r2, c2).value:
                        exist_flag = True
                        if sheet1.cell(r1, 3).value != sheet2.cell(r2, 3).value:
                            info_dict_old.update({'Item Descriptiop:': str(sheet1.cell(r1, 3).value)})
                            info_dict_new.update({'Item Descriptiop:': str(sheet2.cell(r2, 3).value)})
                            sheet2.cell(r2, 3).style = modify_sty
                            sheet2.cell(r2, 12).value = 1

                        if sheet1.cell(r1, 5).value != sheet2.cell(r2, 5).value:
                            info_dict_old.update({'Manufacturer:': str(sheet1.cell(r1, 5).value)})
                            info_dict_new.update({'Manufacturer:': str(sheet2.cell(r2, 5).value)})
                            sheet2.cell(r2, 5).style = modify_sty
                            sheet2.cell(r2, 12).value = 1

                        if sheet1.cell(r1, 6).value != sheet2.cell(r2, 6).value:
                            info_dict_old.update({'MFR PN:': str(sheet1.cell(r1, 6).value)})
                            info_dict_new.update({'MFR PN:': str(sheet2.cell(r2, 6).value)})
                            sheet2.cell(r2, 6).style = modify_sty
                            sheet2.cell(r2, 12).value = 1
                            
                        # if include blank space
                        for c in str(sheet1.cell(r1, 6).value).strip():
                            if c.isspace():
                                # print(sheet1.cell(r1, 6).value + ' has blank space ...')
                                sheet2.cell(r2, 6).style = has_blank_space_sty
                                sheet2.cell(r2, 12).value = 1
                                break

                        if sheet1.cell(r1, 7).value != sheet2.cell(r2, 7).value:
                            info_dict_old.update({'QTY:': str(sheet1.cell(r1, 7).value)})
                            info_dict_new.update({'QTY:': str(sheet2.cell(r2, 7).value)})
                            sheet2.cell(r2, 7).style = modify_sty
                            sheet2.cell(r2, 12).value = 1

                        #clean data
                        if sheet1.cell(r1, 8).value == None:
                            tmp1_lst = []
                        else:
                            tmp1_lst = str(sheet1.cell(r1, 8).value.replace('\n','')).split(',')
                        
                        if sheet2.cell(r2, 8).value == None:
                            tmp2_lst = []
                        else:
                            tmp2_lst = str(sheet2.cell(r2, 8).value.replace('\n','')).split(',')
                        
                        tmp1_set = set(tmp1_lst) 
                        tmp2_set = set(tmp2_lst)
                        #if sheet1.cell(r1, 8).value != sheet2.cell(r2, 8).value:
                        if tmp1_set != tmp2_set:
                            info_dict_old.update({'REF-DES:': str(sheet1.cell(r1, 8).value)})
                            info_dict_new.update({'REF-DES:': str(sheet2.cell(r2, 8).value)})
                            sheet2.cell(r2, 8).style = modify_sty
                            sheet2.cell(r2, 12).value = 1
                            # get diff from field 'REF-DES---------------
                            list1 = str(sheet1.cell(r1, 8).value.replace('\n','')).split(',')
                            list2 = str(sheet2.cell(r2, 8).value.replace('\n','')).split(',')
                            set1 = set(list1)
                            set2 = set(list2)
                            set_same = set1 & set2
                            set_removed = set1 - set_same
                            set_added = set2 - set_same
                            # add diff info to excel file---------------
                            if set_removed:
                                sheet2.cell(r2, 9).value = str(set_removed).replace('\'','').replace('{','').replace('}','').replace(' ','')
                                sheet2.cell(r2, 9).style = remove_sty
                                sheet2.cell(r2, 12).value = 1
                            if set_added:
                                sheet2.cell(r2, 10).value = str(set_added).replace('\'','').replace('{','').replace('}','').replace(' ','')
                                sheet2.cell(r2, 10).style = new_sty
                                sheet2.cell(r2, 12).value = 1

            if exist_flag:
                if info_dict_new:
                    report.write('\n' + 'item: ' + str(sheet1.cell(r1,4).value) + ' ... (diff)')
                    diff_count = diff_count + 1
                    report.write('\n Old Item: ' + str(info_dict_old))
                    report.write('\n New Item: ' + str(info_dict_new))
                    if set_removed:
                        report.write('\n' + 'REMOVED: ' + str(set_removed))
                    else:
                        report.write('\n' + 'REMOVED: ---')
                    if set_added:
                        report.write('\n' + 'ADDED: ' + str(set_added))
                    else:
                        report.write('\n' + 'ADDED: ---')
                    report.write('\n' + '-'*60)
                    # clear
                    set_removed = {}
                    set_added = {}
            else:
                report.write('\n' + 'item: ' + str(sheet1.cell(r1, 4).value) + ' NOT exist...(removed)')
                remove_count = remove_count + 1
                report.write('\n' + '-'*60)
                #removed items save to a list ...
                removed_item = [str(sheet1.cell(r1, 1).value), str(sheet1.cell(r1, 2).value), str(sheet1.cell(r1, 3).value),
                               str(sheet1.cell(r1, 4).value), str(sheet1.cell(r1, 5).value), str(sheet1.cell(r1, 6).value),
                               str(sheet1.cell(r1, 7).value), str(sheet1.cell(r1, 8).value)]
                removed_list.append(removed_item)
                # print('-'*60)

# NEW ITEM, MARKED GREEN#########################################
for r2 in range(2, rows2 + 1):
    found_flag = True
#     print(sheet2.cell(r2, 4).value)
    for r1 in range(2, rows1 + 1):
        if sheet2.cell(r2, 4).value == sheet1.cell(r1, 4).value:
            found_flag = True
            break
        else:
            found_flag = False

    if not found_flag:
        report.write('\n' + 'item: ' + str(sheet2.cell(r2, 4).value) + ' FOUND ... (new)')
        new_count = new_count + 1
        report.write('\n' + '-'*60)
        sheet2.cell(r2, 1).style = new_sty
        sheet2.cell(r2, 2).style = new_sty
        sheet2.cell(r2, 3).style = new_sty
        sheet2.cell(r2, 4).style = new_sty
        sheet2.cell(r2, 5).style = new_sty
        sheet2.cell(r2, 6).style = new_sty
        sheet2.cell(r2, 7).style = new_sty
        sheet2.cell(r2, 8).style = new_sty
        sheet2.cell(r2, 12).value = 1

# REMOVED ITEM, MARKED RED#########################################
# APPEND TO EXCEl FILE TAIL
n = 1
for item in removed_list:
    sheet2.cell(rows2 + n, 1).value = item[0]
    sheet2.cell(rows2 + n, 1).style = remove_sty
    sheet2.cell(rows2 + n, 2).value = item[1]
    sheet2.cell(rows2 + n, 2).style = remove_sty
    sheet2.cell(rows2 + n, 3).value = item[2]
    sheet2.cell(rows2 + n, 3).style = remove_sty
    sheet2.cell(rows2 + n, 4).value = item[3]
    sheet2.cell(rows2 + n, 4).style = remove_sty
    sheet2.cell(rows2 + n, 5).value = item[4]
    sheet2.cell(rows2 + n, 5).style = remove_sty
    sheet2.cell(rows2 + n, 6).value = item[5]
    sheet2.cell(rows2 + n, 6).style = remove_sty
    sheet2.cell(rows2 + n, 7).value = item[6]
    sheet2.cell(rows2 + n, 7).style = remove_sty
    sheet2.cell(rows2 + n, 8).value = item[7]
    sheet2.cell(rows2 + n, 8).style = remove_sty
    sheet2.cell(r2, 12).value = 1
    n = n + 1


report.write('\n')
report.write('\n' + '-'*60)
report.write('\n')
report.write('\n' + 'Diff: ' + str(diff_count))
report.write('\n' + 'New: ' + str(new_count))
report.write('\n' + 'Remove: ' + str(remove_count))

# SAVE AS BOM_COMPARE FILE#########################################
wb2.save('./BOM_COMPARE.xlsx')
report.close()


# only save diff info to BOM_COMPARE_diff.xlsx
wb3 = load_workbook("BOM_COMPARE.xlsx")
sheet3 = wb3.active
rows3 = sheet3.max_row
cols3 = sheet3.max_column
del_rows = []
for r3 in range(2, rows3 + 1):
    if sheet3.cell(r3, 12).value is None:
        del_rows.append(r3)

for r in reversed(del_rows):
    sheet3.delete_rows(r)


wb3.save('./BOM_COMPARE_diff.xlsx')


print('Compare completely!')
print('For Detail see BOM_REPORT.TXT, BOM_COMPARE.xlsx and BOM_COMPARE_diff.xlsx ...')
print('')
compare_done = input('Press any key to continue ...')
